package com.hschinese.pronunciation.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.hschinese.pronunciation.bean.AnswerRecordBean;
import com.hschinese.pronunciation.bean.AppDataBean;
import com.hschinese.pronunciation.bean.LearnRecordBean;
import com.hschinese.pronunciation.bean.TestResultBean;
import com.hschinese.pronunciation.utils.Constant;
import com.hschinese.pronunciation.utils.Logger;


import java.util.List;

/**
 * Created by wo on 2015/10/29.
 */
public class DBHelper extends SQLiteOpenHelper {
    private Context context = null;
    private static DBHelper mDBHelper;
    private SQLiteDatabase mDatabase;
    public static String RES_TABLE = "resource_table";
    public static String TEST_RESULT_TABLE = "test_result_table";
    public static String ANSWER_RECORD = "answer_record";
    public static String LEARN_RECORD = "learn_record";

    public static DBHelper getInstance(Context ctx){
        synchronized (DBHelper.class) {
            if (mDBHelper == null) {
                mDBHelper = new DBHelper(ctx);
            }
            return mDBHelper;
        }
    }

    public DBHelper(Context ctx) {
        super(ctx, Constant.DATABASE_NAME, null, Constant.DATABASE_VERSION);
        context = ctx;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATA_TABLE_RESOURCES.toString());
        db.execSQL(CREATE_TABLE_TONE_KNOWLEDGES.toString());
        db.execSQL(CREATE_TABLE_ANSWER_RECORD.toString());
        db.execSQL(CREATE_TABLE_LEARN_RECORD.toString());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

    private SQLiteDatabase getmDb(Context ctx) {
        if(null == mDatabase){
            mDatabase = getInstance(ctx).getWritableDatabase();
        }
        return mDatabase;
    }

    private StringBuffer CREATA_TABLE_RESOURCES = new StringBuffer("create table if not EXISTS " + RES_TABLE + "(")
            .append("id integer primary key autoincrement,")
            .append("chinese text, pinyin text, phone text,")
            .append("tone text, audioName text, picName text,")
            .append("type integer, groupId integer, weight integer")
            .append(");");

    private StringBuffer CREATE_TABLE_TONE_KNOWLEDGES = new StringBuffer("CREATE TABLE IF NOT EXISTS " + TEST_RESULT_TABLE + "(")
            .append("id integer primary key autoincrement,")
            .append("tone integer, rightTimes integer, wrongTimes integer,")
            .append("wrongTones integer, type integer, weight integer")
            .append(");");

    private StringBuffer CREATE_TABLE_ANSWER_RECORD = new StringBuffer("CREATE TABLE IF NOT EXISTS " + ANSWER_RECORD + "(")
            .append("id integer primary key autoincrement,")
            .append("recordID text,")
            .append("chinese text,")
            .append("rightTimes integer,")
            .append("wrongTimes integer,")
            .append("result text,")
            .append("userAnswer text,")
            .append("timestamp text")
            .append(");");

    private StringBuffer CREATE_TABLE_LEARN_RECORD = new StringBuffer("CREATE TABLE IF NOT EXISTS " + LEARN_RECORD + "(")
            .append("id integer primary key autoincrement,")
            .append("recordID integer,")
            .append("chinese text,")
            .append("learnedTimes integer,")
            .append("learnType text,")
            .append("timestamp text")
            .append(");");

    // 插入数据
    public void insertAppData(List<AppDataBean> l) {
        mDatabase = getmDb(context);
        for (AppDataBean res : l) {
            if (res == null) return;
            ContentValues values = new ContentValues();
            values.put("chinese", res.getChinese());
            values.put("pinyin", res.getPinyin());
            values.put("phone", res.getPhone());
            values.put("tone", res.getTone());
            values.put("audioName", res.getAudioName());
            values.put("picName", res.getPicName());
            values.put("type", res.getType());
            values.put("groupID", res.getGroupID());
            values.put("weight", res.getWeight());
            mDatabase.insert(RES_TABLE, null, values);
        }
    }

    public void insetTestResult(List<TestResultBean> l) {
        mDatabase = getmDb(context);
        for (TestResultBean res : l) {
            if (res == null) return;
            ContentValues values = new ContentValues();
            values.put("tone", res.getTone());
            values.put("rightTimes", res.getRightTimes());
            values.put("wrongTimes", res.getWrongTimes());
            values.put("wrongTones", res.getWrongTones());
            values.put("type", res.getType());
            values.put("weight", res.getWeight());
            mDatabase.insert(TEST_RESULT_TABLE, null, values);
        }
    }

    /**
     * 查询表中的该项数据是否存在，多条件用and
     * @param type
     * @param table
     */
    public Cursor query(String table, String wherearg, String values[]) {
        Cursor cursor = null;
        mDatabase = getmDb(context);
        cursor = mDatabase.query(table, null, wherearg, values, null, null, null);
        return cursor;
    }

    /**
     * 插入practise数据到数据表中, 先查询该错误拼音是否存在，存在就更新数据项
     * @param table
     * @param data
     * @return
     */
    public boolean insert(String table, TestResultBean data) {
        mDatabase = getmDb(context);
        if (isDataExisted(table, data)) {
            return true;
        }
        boolean b = false;
        ContentValues values = new ContentValues();
        values.put("tone", data.getTone());
        values.put("rightTimes", data.getRightTimes());
        values.put("wrongTimes", data.getWrongTimes());
        values.put("wrongTones", data.getWrongTones());
        values.put("type", data.getType());
        values.put("weight", data.getWeight());
        long r = mDatabase.insert(table, null, values );
        if (r == -1) {
            b = false;
        } else b = true;
        return b;
    }

    private boolean isDataExisted(String table, TestResultBean data) {
        Cursor cursor = null;
        cursor = mDatabase.query(table, null, " tone=? ", new String[]{data.getTone() + ""}, null, null, null);
        if (cursor.getCount() <= 0)
            return false;
        else return true;
    }

    /**
     * 需要标识具体是哪个数据项的添加，或者数据传递过来时是已经更新好了的
     * @param table
     * @param res
     */
    public void upgrade(String table, TestResultBean res) {
        ContentValues values = new ContentValues();
        values.put("rightTimes", res.getRightTimes());
        values.put("wrongTimes", res.getWrongTimes());
        mDatabase.update(table, values, " tone=?", new String[]{res.getTone()});
    }

    public void insertAnswer(AnswerRecordBean data) {
        // 先检查数据表中是否有该题的记录
        Cursor cursor = null;
        cursor = mDatabase.query(ANSWER_RECORD, null, " chinese=? ", new String[]{data.getChinese() + ""}, null, null, null);
        if (cursor.getCount() <= 0) { // TODO 不存在，需要将数据添加到数据库中
            ContentValues values = new ContentValues();
            values.put("recordID", data.getRecordID());
            values.put("chinese", data.getChinese());
            values.put("rightTimes", data.getRightTimes());
            values.put("wrongTimes", data.getWrongTimes());
            values.put("result", data.getResult());
            values.put("userAnswer", data.getUserAnswer());
            values.put("timestamp", data.getTimestamp());
            mDatabase.insert(ANSWER_RECORD, null, values);
        } else { // update 对/错数量的更新
            cursor.moveToFirst();
            if (data.getRightTone().equals(data.getUserAnswer())) {
                int rightTimes = cursor.getInt(cursor.getColumnIndex("rightTimes"));
                ContentValues values = new ContentValues();
                values.put("rightTimes", rightTimes + 1);
                mDatabase.update(ANSWER_RECORD, values, " chinese=? ", new String[]{data.getChinese() + ""});
            }
        }
        cursor.close();
    }

    public void insertLearnRecord(LearnRecordBean data) {
        // 先检查数据表中是否有该题的记录
        Cursor cursor = null;
        cursor = mDatabase.query(LEARN_RECORD, null, " chinese=? and learnType=? ", new String[]{data.getChinese() + "", data.getLearnType()}, null, null, null);
        if (cursor.getCount() <= 0) { // TODO 不存在，需要将数据添加到数据库中
            ContentValues values = new ContentValues();
            values.put("recordID", data.getRecordID());
            values.put("chinese", data.getChinese());
            values.put("learnedTimes", data.getLearnedTimes());
            values.put("learnType", data.getLearnType());
            values.put("timestamp", data.getTimestamp());
            mDatabase.insert(LEARN_RECORD, null, values);
        } else {
            cursor.moveToFirst();
            int learnedTimes = cursor.getInt(cursor.getColumnIndex("learnedTimes"));
            ContentValues values = new ContentValues();
            values.put("learnedTimes", learnedTimes + 1);
            mDatabase.update(LEARN_RECORD, values, " chinese=? and learnType=? ", new String[]{data.getChinese() + "", data.getLearnType()});
        }
        cursor.close();
    }

    public void dropAnswerTable() {
        String sql = "DROP TABLE " + ANSWER_RECORD;
//        mDatabase.execSQL(sql);
//        mDatabase.execSQL(CREATE_TABLE_ANSWER_RECORD.toString());
    }

    public void drapLearnTable() {
        String sql = "DROP TABLE " + LEARN_RECORD;
//        mDatabase.execSQL(sql);
//        mDatabase.execSQL(CREATE_TABLE_LEARN_RECORD.toString());
    }

    public void destroy() {
        if (mDatabase != null) {
            mDatabase.close();
            mDatabase = null;
        }
    }
}
